1 Imports System.Data.SqlClient
2 Imports System.Security.Cryptography
3 Imports System.Text
4 Imports System.IO
5 Public Class frmAttendance
6 Sub Reset()
7 WorkingDate.Text = Today
8 StaffID.Text = ""
9 StaffName.Text = ""
10 InTime.Text = Now
11 OutTime.Text = Now
12 Status.SelectedIndex = -1
13 txtStaff.Text = ""
14 GetData()
15 btnDelete.Enabled = False
16 btnUpdate.Enabled = False
17 txtOutTime.Visible = False
18 txtInTime.Visible = False
19 btnSave.Enabled = True
20 WorkingDate.Enabled = True
21 InTime.Enabled = False
22 OutTime.Enabled = False
23 End Sub
24 Sub GetData()
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 Dim sql As String = "SELECT RTRIM(Staff.St_ID) ,RTRIM(Staff.StaffID),RTRIM(StaffName) from Staff order by StaffName"
29 cmd = New SqlCommand(sql, con)
30 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
31 dgw.Rows.Clear()
32 While (rdr.Read() = True)
33 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
34 End While
35 con.Close()
36 Catch ex As Exception
37 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
38 End Try
39 End Sub
40 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
41 If Len(Trim(StaffID.Text)) = 0 Then
42 MessageBox.Show("Please retrieve Staff id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
43 StaffID.Focus()
44 Exit Sub
45 End If
46 If Len(Trim(Status.Text)) = 0 Then
47 MessageBox.Show("Please select Status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
48 Status.Focus()
49 Exit Sub
50 End If
51 Try
52 con = New SqlConnection(cs)
53 con.Open()
54 Dim ct As String = "select StaffID,workingdate from StaffAttendance where StaffID=" & txtStID.Text & " and workingdate=@d1"
55 cmd = New SqlCommand(ct)
56 cmd.Parameters.AddWithValue("@d1", WorkingDate.Text)
57 cmd.Connection = con
58 rdr = cmd.ExecuteReader()
59 If rdr.Read Then
60 MessageBox.Show("Staff today's attendance is already saved", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
61 If Not rdr Is Nothing Then
62 rdr.Close()
63 End If
64 Exit Sub
65 End If
66 auto()
67 con = New SqlConnection(cs)
68 con.Open()
69 Dim cb As String = "insert into StaffAttendance(ID,Workingdate,StaffID,status,intime,outtime) VALUES (" & txtID.Text & ",@d1,@d2,@d3,@d4,@d5)"
70 cmd = New SqlCommand(cb)
71 cmd.Connection = con
72 cmd.Parameters.AddWithValue("@d1", WorkingDate.Text)
73 cmd.Parameters.AddWithValue("@d2", txtStID.Text)
74 cmd.Parameters.AddWithValue("@d3", Status.Text)
75 If Status.Text = "P" Then
76 cmd.Parameters.AddWithValue("@d4", InTime.Text)
77 cmd.Parameters.AddWithValue("@d5", OutTime.Text)
78 ElseIf Status.Text = "A" Then
79 cmd.Parameters.AddWithValue("@d4", txtInTime.Text)
80 cmd.Parameters.AddWithValue("@d5", txtOutTime.Text)
81 End If
82 cmd.ExecuteReader()
83 If con.State = ConnectionState.Open Then
84 con.Close()
85 End If
86 con.Close()
87 Dim st As String = "added the new attendance entry having id '" & txtID.Text & "'"
88 LogFunc(lblUser.Text, st)
89 MessageBox.Show("Successfully saved", "Entry", MessageBoxButtons.OK, MessageBoxIcon.Information)
90 btnSave.Enabled = False
91 con.Close()
92 GetData()
93 Catch ex As Exception
94 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
95 End Try
96 End Sub
97
98 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
99 If Len(Trim(StaffID.Text)) = 0 Then
100 MessageBox.Show("Please retrieve Staff id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
101 StaffID.Focus()
102 Exit Sub
103 End If
104 If Len(Trim(Status.Text)) = 0 Then
105 MessageBox.Show("Please select Status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
106 Status.Focus()
107 Exit Sub
108 End If
109 Try
110 con = New SqlConnection(cs)
111 con.Open()
112 Dim cb As String = "Update StaffAttendance set StaffID=@d2,status=@d3,intime=@d4,outtime=@d5 where ID=" & txtID.Text & ""
113 cmd = New SqlCommand(cb)
114 cmd.Connection = con
115 cmd.Parameters.AddWithValue("@d2", txtStID.Text)
116 cmd.Parameters.AddWithValue("@d3", Status.Text)
117 If Status.Text = "P" Then
118 cmd.Parameters.AddWithValue("@d4", InTime.Text)
119 cmd.Parameters.AddWithValue("@d5", OutTime.Text)
120 ElseIf Status.Text = "A" Then
121 cmd.Parameters.AddWithValue("@d4", txtInTime.Text)
122 cmd.Parameters.AddWithValue("@d5", txtOutTime.Text)
123 End If
124 cmd.ExecuteReader()
125 If con.State = ConnectionState.Open Then
126 con.Close()
127 End If
128 con.Close()
129 Dim st As String = "updated the attendance entry having id '" & txtID.Text & "'"
130 LogFunc(lblUser.Text, st)
131 MessageBox.Show("Successfully updated", "Entry", MessageBoxButtons.OK, MessageBoxIcon.Information)
132 btnUpdate.Enabled = False
133 con.Close()
134 GetData()
135 Catch ex As Exception
136 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
137 End Try
138 End Sub
139
140 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
141 Try
142 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
143 DeleteRecord()
144 End If
145 Catch ex As Exception
146 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
147 End Try
148 End Sub
149 Private Sub DeleteRecord()
150 Try
151 Dim RowsAffected As Integer = 0
152 con = New SqlConnection(cs)
153 con.Open()
154 Dim cq As String = "delete from StaffAttendance where id=" & txtID.Text & ""
155 cmd = New SqlCommand(cq)
156 cmd.Connection = con
157 RowsAffected = cmd.ExecuteNonQuery()
158 If RowsAffected > 0 Then
159 Dim st As String = "deleted the attendance entry having id '" & txtID.Text & "'"
160 LogFunc(lblUser.Text, st)
161 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
162 GetData()
163 Reset()
164 Else
165 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
166 Reset()
167 End If
168 If con.State = ConnectionState.Open Then
169 con.Close()
170
171 End If
172 Catch ex As Exception
173 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
174 End Try
175 End Sub
176
177 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
178 Me.Close()
179 End Sub
180
181 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
182 Reset()
183 End Sub
184
185 Private Sub frmAdvanceEntry_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
186 GetData()
187 End Sub
188
189 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
190 Try
191 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
192 txtStID.Text = dr.Cells(0).Value.ToString
193 StaffID.Text = dr.Cells(1).Value.ToString
194 StaffName.Text = dr.Cells(2).Value.ToString
195 Catch ex As Exception
196 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
197 End Try
198 End Sub
199
200 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
201 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
202 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
203 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
204 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
205 End If
206 Dim b As Brush = SystemBrushes.ControlText
207 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
208
209 End Sub
210
211 Private Sub auto()
212 Dim Num As Integer = 0
213 con = New SqlConnection(cs)
214 con.Open()
215 Dim sql As String = ("SELECT MAX(ID) FROM StaffAttendance")
216 cmd = New SqlCommand(sql)
217 cmd.Connection = con
218 If (IsDBNull(cmd.ExecuteScalar)) Then
219 Num = 1
220 txtID.Text = Num.ToString
221 Else
222 Num = cmd.ExecuteScalar + 1
223 txtID.Text = Num.ToString
224 End If
225 cmd.Dispose()
226 con.Close()
227 con.Dispose()
228 End Sub
229 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
230 frmAttendanceEntryRecord.lblSet.Text = "Attendance Entry"
231 frmAttendanceEntryRecord.Reset()
232 frmAttendanceEntryRecord.ShowDialog()
233 End Sub
234
235 Private Sub Status_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles Status.SelectedIndexChanged
236 If Status.Text = "P" Then
237 txtOutTime.Visible = False
238 txtInTime.Visible = False
239 InTime.Enabled = True
240 OutTime.Enabled = True
241 InTime.Text = Now
242 OutTime.Text = Now
243
244 ElseIf Status.Text = "A" Then
245 txtOutTime.Visible = True
246 txtInTime.Visible = True
247 txtOutTime.Text = "00:00:00"
248 txtInTime.Text = "00:00:00"
249 End If
250 End Sub
251
252 Private Sub txtStaff_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaff.TextChanged
253 Try
254 con = New SqlConnection(cs)
255 con.Open()
256 Dim sql As String = "SELECT RTRIM(Staff.St_ID) ,RTRIM(Staff.StaffID),RTRIM(StaffName) from Staff where StaffName like '" & txtStaff.Text & "%' order by StaffName"
257 cmd = New SqlCommand(sql, con)
258 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
259 dgw.Rows.Clear()
260 While (rdr.Read() = True)
261 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
262 End While
263 con.Close()
264 Catch ex As Exception
265 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
266 End Try
267 End Sub
268 End Class
